const express = require("express");
const pool = require("../pool.js");

const supplyRouter = express.Router();
module.exports = supplyRouter;
// 查找所有商品库存不为0
supplyRouter.get("/findAll", (req, res, next) => {
  var page = req.query.page || 1;
  const pagesize = 10;
  var offset = (page - 1) * pagesize;
  var sql = `select count(sid) as count from supply where sstore>0`;
  pool.query(sql, (err, r) => {
    if (err) {
      next(err);
      return;
    }
    let pagecount = Math.ceil(r[0].count / pagesize);
    var sql = `select * from supply where sstore>0 limit ?,?`;
    pool.query(sql, [offset, pagesize], (err, r) => {
      if (err) {
        next(err);
        return;
      }
      res.send({ code: 200, data: r, pagecount });
    });
  });
});
// 根据id查找商品
supplyRouter.get("/find", (req, res, next) => {
  var sid = req.query.sid;
  if (sid == "" || sid == undefined) {
    res.send({ code: 202, msg: "id不可为空" });
    return;
  }
  var sql = `select * from supply where sid=?`;
  pool.query(sql, [sid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) res.send({ code: 200, data: r });
    else res.send({ code: 201, msg: "无数据" });
  });
});
// 根据id查找商品价格
supplyRouter.get("/findPrice", (req, res, next) => {
  var sid = req.query.sid;
  if (sid == "" || sid == undefined) {
    res.send({ code: 202, msg: "id不可为空" });
    return;
  }
  var sql = `select sprice from supply where sid=?`;
  pool.query(sql, [sid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      let price = r[0].sprice;
      res.send({ code: 200, price });
    } else {
      res.send({ code: 201, msg: "找不到商品" });
    }
  });
});
// 新增商品
supplyRouter.post("/insert", (req, res, next) => {
  var sname = req.body.sname;
  var spro = req.body.spro;
  var simg = req.body.simg;
  var sprice = req.body.sprice;
  var sstate = req.body.sstate;
  var sstore = req.body.sstore;
  var scomp = req.body.scomp;
  var stime = req.body.stime;
  var sweight = req.body.sweight;
  if (
    sname == "" ||
    sname == undefined ||
    spro == "" ||
    spro == undefined ||
    simg == "" ||
    simg == undefined ||
    sprice == "" ||
    sprice == undefined ||
    sstate == "" ||
    sstate == undefined ||
    sstore == "" ||
    sstore == undefined
  ) {
    res.send({ code: 202, msg: "输入不可为空" });
    return;
  }
  // 先查查库存中有没有
  var sql = `select sid from supply where sname=?`;
  pool.query(sql, [sname], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      // 如果有，就改成修改数量
      sql = `update supply set sstore=sstore+? where sname=?`;
      pool.query(sql, [sstore, sname], (err, r) => {
        if (err) {
          next(err);
          return;
        }
        if (r.affectedRows) {
          res.send({ code: 200, msg: "进货成功" });
        } else {
          res.send({ code: 501, msg: "数据库故障" });
        }
      });
    } else {
      sql = `insert into supply values(null,?,?,?,?,?,?,?,?,?)`;
      pool.query(
        sql,
        [sname, spro, simg, sprice, sstate, sstore, scomp, stime, sweight],
        (err, r) => {
          if (err) {
            next(err);
            return;
          }
          if (r.affectedRows != 0) {
            res.send({ code: 200, msg: "新增商品成功" });
          } else {
            res.send({ code: 201, msg: "新增商品失败" });
          }
        }
      );
    }
  });
});
// 删除商品
supplyRouter.delete("/delete", (req, res, next) => {
  var sid = req.query.sid;
  if (sid == "" || sid == undefined) {
    res.send({ code: 202, msg: "输入不能为空" });
    return;
  }
  var sql = `delete from supply where sid=?`;
  pool.query(sql, [sid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.affectedRows != 0) {
      res.send({ code: 200, msg: "删除成功" });
    } else {
      res.send({ code: 201, msg: "删除失败" });
    }
  });
});
// 更新商品数据
supplyRouter.put("/update", (req, res, next) => {
  var sid = req.body.sid;
  var sname = req.body.sname;
  var spro = req.body.spro;
  var simg = req.body.simg;
  var sprice = req.body.sprice;
  var sstore = req.body.sstore;
  var sstate = req.body.sstate;
  var scomp = req.body.scomp;
  var stime = req.body.stime;
  var sweight = req.body.sweight;
  if (
    sid == "" ||
    sid == undefined ||
    sname == "" ||
    sname == undefined ||
    spro == "" ||
    spro == undefined ||
    simg == "" ||
    simg == undefined ||
    sprice == "" ||
    sprice == undefined ||
    sstate == "" ||
    sstate == undefined ||
    sstore == "" ||
    sstore == undefined
  ) {
    res.send({ code: 202, msg: "输入不可为空" });
    return;
  }
  var sql = `update supply set sname=?, spro=?,simg=?,sstate=?,sstore=?,scomp=?,stime=?,sweight=? where sid=?`;
  pool.query(
    sql,
    [sname, spro, simg, sstate, sstore, scomp, stime, sweight, sid],
    (err, r) => {
      if (err) {
        next(err);
        return;
      }
      if (r.affectedRows != 0) {
        res.send({ code: 200, msg: "更新成功" });
      } else {
        res.send({ code: 201, msg: "更新失败" });
      }
    }
  );
});
// 更新商品状态
supplyRouter.put("/updateState", (req, res, next) => {
  var sid = req.body.sid;
  var newstate = req.body.newstate;
  if (
    sid == "" ||
    sid == undefined ||
    newstate == "" ||
    newstate == undefined
  ) {
    res.send({ code: 202, msg: "输入不能为空" });
    return;
  }
  var sql = `update supply set sstate=? where sid=?`;
  pool.query(sql, [newstate, sid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.affectedRows != 0) {
      res.send({ code: 200, msg: "更新成功" });
    } else {
      res.send({ code: 201, msg: "更新失败" });
    }
  });
});
// 只更新商品库存:进货
supplyRouter.put("/updateCount", (req, res, next) => {
  var sname = req.body.sname;
  var count = req.body.count;
  if (sname == "" || sname == undefined || count == "" || count == undefined) {
    res.send({ code: 202, msg: "数据不能为空" });
    return;
  }
  var sql = `select sid from supply where sname=?`;
  pool.query(sql, [sname], (err, r) => {
    if (err) {
      next(err);
    }
    if (r.length != 0) {
      sql = `update supply set sstore=sstore+? where sname=?`;
      pool.query(sql, [count, sname], (err, r) => {
        if (err) {
          next(err);
          return;
        }
        if (r.affectedRows) {
          res.send({ code: 200, msg: "进货成功" });
        } else {
          res.send({ code: 501, msg: "进货失败" });
        }
      });
    } else {
      res.send({ code: 201, msg: "库存中不存在此商品" });
    }
  });
});
